// Copyright 2022 a.o.smith ALL Rights Reserved
package com.liuxinlong.utils;

import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.TableStyle;
import com.liuxinlong.common.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入导出工具类
 *
 * @author liuxinlong@hotwater.com.cn
 * @version 1.0.0
 * @since 2022-01-14
 */
@Slf4j
public class ExcelUtils {
    /**
     * 导出Excel
     *
     * @param response
     * @param data
     */
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info("导出解析开始，fileName:{}", data.getFileName());
        try {
            // 实例化HSSFWorkbook
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 创建一个Excel表单，参数为sheet的名字
            XSSFSheet sheet = workbook.createSheet("sheet");
            // 设置表头
            setTitle(workbook, sheet, data.getHead());
            // 设置单元格并赋值
            setData(sheet, data.getData());
            // 设置浏览器下载
            setBrowser(response, workbook, data.getFileName());
            log.info("导出解析成功!");
        } catch (Exception e) {
            log.info("导出解析失败!");
            e.printStackTrace();
        }
    }

    /**
     * 导出多sheet Excel
     *
     * @param response 返回信息
     * @param dataList 数据列表
     * @param fileName 文件名称
     */
    public static void exportExcel(HttpServletResponse response, List<ExcelData> dataList, String fileName) {
        log.info("导出解析开始，fileName:{}", fileName);
        if (ObjectUtils.isEmpty(dataList)) {
            return;
        }
        try {
            // 实例化HSSFWorkbook
            XSSFWorkbook workbook = new XSSFWorkbook();
            for (ExcelData data : dataList) {
                log.info("导出解析sheet开始，sheetName:{}", data.getFileName());
                // 创建一个Excel表单，参数为sheet的名字
                XSSFSheet sheet = workbook.createSheet(data.getFileName());
                // 设置表头
                setTitle(workbook, sheet, data.getHead());
                // 设置单元格并赋值
                setData(sheet, data.getData());
            }
            // 设置浏览器下载
            setBrowser(response, workbook, fileName);
            log.info("导出解析成功!");
        } catch (Exception e) {
            log.info("导出解析失败!");
            e.printStackTrace();
        }
    }

    /**
     * 设置表头
     *
     * @param workbook
     * @param sheet
     * @param str
     */
    private static void setTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] str) {
        try {
            XSSFRow row = sheet.createRow(0);
            // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            // 设置为居中加粗,格式化时间格式
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            // 创建表头名称
            XSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("导出时设置表头失败！");
            e.printStackTrace();
        }
    }

    /**
     * 表格赋值
     *
     * @param sheet
     * @param data
     */
    private static void setData(XSSFSheet sheet, List<String[]> data) {
        try {
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                }
                rowNum++;
            }
            log.info("表格赋值成功！");
        } catch (Exception e) {
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }

    /**
     * 使用浏览器下载
     *
     * @param response
     * @param workbook
     * @param fileName
     */
    private static void setBrowser(HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
        try {
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            // 将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("设置浏览器下载成功！");
        } catch (Exception e) {
            log.info("设置浏览器下载失败！");
            e.printStackTrace();
        }

    }

    /**
     * 导入数据解析
     *
     * @param fileName 文件名称
     * @param startRow 开始解析行数
     * @return 解析数据
     */
    public static List<String[]> importExcel(String fileName, int startRow) {
        log.info("导入解析开始，fileName:{}", fileName);
        try {
            List<String[]> list = new ArrayList<>();
            InputStream inputStream = new FileInputStream(fileName);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            System.out.println("sheetName: " + sheet.getSheetName());
            // 获取sheet的行数
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                // 过滤表头行
                if (i < startRow) {
                    continue;
                }
                // 获取当前行的数据
                Row row = sheet.getRow(i);
                String[] objects = new String[row.getPhysicalNumberOfCells()];
                int index = 0;
                boolean allEmpty = true;
                for (Cell cell : row) {
                    objects[index] = getCellValue(cell);
//                    if (cell.getCellType().equals(CellType.NUMERIC)) {
//                        objects[index] = String.valueOf(cell.getNumericCellValue());
//                    }
//                    if (cell.getCellType().equals(CellType.STRING)) {
//                        objects[index] = cell.getStringCellValue();
//                    }
//                    if (cell.getCellType().equals(CellType.BOOLEAN)) {
//                        objects[index] = String.valueOf(cell.getBooleanCellValue());
//                    }
//                    if (cell.getCellType().equals(CellType.ERROR)) {
//                        objects[index] = String.valueOf(cell.getErrorCellValue());
//                    }

                    if (StringUtils.isNotEmpty(objects[index])) {
                        allEmpty = false;
                    }
                    index++;
                }
                if (!allEmpty) {
                    list.add(objects);
                }
            }
            log.info("导入文件解析成功！");
            return list;
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 设置全局样式
     *
     * @return
     */
    private static TableStyle getTableStyle() {
        TableStyle tableStyle = new TableStyle();

        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setBold(true);
        font.setFontHeightInPoints((short) 9);
        tableStyle.setTableHeadFont(font);
        Font fontContent = new Font();
        fontContent.setFontHeightInPoints((short) 9);
        tableStyle.setTableContentFont(fontContent);

        return tableStyle;
    }

    /**
     * 获取单元格数据
     *
     * @param cell 单元格信息
     * @return 单元格数据
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                //short s = cell.getCellStyle().getDataFormat();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        throw new RuntimeException("日期格式错误!!!");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
//                    DecimalFormat format = new DecimalFormat("0.00");
//                    cellValue = String.valueOf(format.format(cell.getNumericCellValue()));
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = null;
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static Map<String, XSSFPictureData> getPictures(XSSFSheet xssfSheet){

        Map<String,XSSFPictureData> map=new HashMap<>();
        List<XSSFShape> list=xssfSheet.getDrawingPatriarch().getShapes();

        for (XSSFShape shape:list){

            XSSFPicture picture = (XSSFPicture) shape;
            XSSFClientAnchor xssfClientAnchor=(XSSFClientAnchor) picture.getAnchor();
            XSSFPictureData pdata = picture.getPictureData();
            // 行号-列号
            String key = xssfClientAnchor.getRow1() + "-" + xssfClientAnchor.getCol1();
            log.info("key数据:{}",key);
            map.put(key, pdata);

        }

        return map;
    }

}
